package itheima03;

import domain.Users;
import org.junit.Test;
import utils.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/*
    PreparedStatement完成查询数据
    步骤:
        1.获取连接Connection对象
        2.定义sql语句,参数使用?代替
        3.Connection对象获取执行sql语句的PreparedStatement对象,传递sql语句
        4.PreparedStatement对象调用方法,给sql语句中的?赋值
        5.PreparedStatement对象执行增删改查,获取结果
        6.处理结果
        7.关闭资源

 */
/*
public class Demo04PreparedStatement {
    //根据uid查询记录,封装成Users对象
    @Test
    public void queryUserByUid() throws Exception {
        String uid = "u005";
        //1.获取连接Connection对象
        Connection con = JDBCUtil.getConnection();

        //2.定义sql语句,参数使用?代替
        String sql = "select * from users where uid=?";

        //3.Connection对象获取执行sql语句的PreparedStatement对象,传递sql语句
        PreparedStatement pstmt = con.prepareStatement(sql);

        //4.PreparedStatement对象调用方法,给sql语句中的?赋值
        pstmt.setObject(1, uid);

        //5.PreparedStatement对象执行增删改查,获取结果
        ResultSet rs = pstmt.executeQuery();

        //6.处理结果
        if (rs.next()) {
            //创建Users对象
            Users user = new Users();
            //获取字段值
            uid = rs.getString("uid");
            String uname = rs.getString("uname");
            String upass = rs.getString("upass");
            //给对象赋值
            user.setUid(uid);
            user.setUname(uname);
            user.setUpass(upass);
            System.out.println(user);
        } else {
            System.out.println("没有查询到数据");
        }
        //7.关闭资源
        JDBCUtil.release(con, pstmt, null);
    }

    //查询所有记录,存储List集合对象,泛型Users
    @Test
    public void queryAll() throws Exception {

        //创建List集合对象
        List<Users> list = new ArrayList<>();

        //1.获取连接Connection对象
        Connection con = JDBCUtil.getConnection();

        //2.定义sql语句,参数使用?代替
        String sql = "select * from users";

        //3.Connection对象获取执行sql语句的PreparedStatement对象,传递sql语句
        PreparedStatement pstmt = con.prepareStatement(sql);

        //4.PreparedStatement对象调用方法,给sql语句中的?赋值
        //5.PreparedStatement对象执行增删改查,获取结果
        ResultSet rs = pstmt.executeQuery();


        //6.处理结果
        while(rs.next()) {
            //创建Users对象
            Users user = new Users();
            //获取字段值
            String uid = rs.getString("uid");
            String uname = rs.getString("uname");
            String upass = rs.getString("upass");
            //给对象赋值
            user.setUid(uid);
            user.setUname(uname);
            user.setUpass(upass);
            //添加到集合
            list.add(user);
        }
        //遍历
        if(list.size()==0) {
            System.out.println("没有查询到结果");
        } else {
            for (Users u : list) {
                System.out.println(u);
            }
        }
        //7.关闭资源
        JDBCUtil.release(con, pstmt, rs);

    }
}
*/


public class Demo04PreparedStatement {
    @Test
    public void queryUserByUid() throws SQLException {
        /*Scanner scanner=new Scanner(System.in);
        System.out.println("请输入主键ID:");
        String uid =scanner.nextLine();*/
        String uid ="u001";
        //1.获取连接Connection对象
        Connection connection=JDBCUtil.getConnection();
        //2.定义sql语句,参数使用?代替
        String sql="select * from users where uid=?";
        //3.Connection对象获取执行sql语句的PreparedStatement对象,传递sql语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //4.PreparedStatement对象调用方法,给sql语句中的?赋值
        preparedStatement.setObject(1,uid);
        ResultSet resultSet = preparedStatement.executeQuery();

        //5.PreparedStatement对象执行增删改查,获取结果
        if(resultSet.next()){
         Users users=new Users();
         uid=resultSet.getString("uid");
         String uname=resultSet.getString("uname");
         String upass=resultSet.getString("upass");
         users.setUid(uid);
         users.setUname(uname);
         users.setUpass(upass);
         System.out.println(users);
        }else {
            System.out.println("没有查询到数据");
        }

        //6.处理结果
        //7.关闭资源
        JDBCUtil.release(connection,preparedStatement,resultSet);
    }
    @Test
    public void queryAll() throws SQLException {
        //0.创建集合
        List<Users> list=new ArrayList();
        //1.获取连接Connection对象
        Connection connection=JDBCUtil.getConnection();
        //2.定义sql语句,参数使用?代替
        String sql="select * from  users";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //3.Connection对象获取执行sql语句的PreparedStatement对象,传递sql语句
         ResultSet resultSet = preparedStatement.executeQuery();
        //4.PreparedStatement对象调用方法,给sql语句中的?赋值
        while (resultSet.next()){
            Users users=new Users();
            //获取字段
            String uid=resultSet.getString("uid");
            String uname=resultSet.getString("uname");
            String upass=resultSet.getString("upass");
            users.setUid(uid);
            users.setUpass(upass);
            users.setUname(uname);
            list.add(users);

        }
        if(list.size()!=0){
            for (Users users : list) {
                System.out.println(users);
            }
        }else {
            System.out.println("查询不到数据");
        }


        //5.PreparedStatement对象执行增删改查,获取结果
        //6.处理结果
        //7.关闭资源
        JDBCUtil.release(connection,preparedStatement,resultSet);
    }
}
